<?php
namespace app\admin\controller;

use app\admin\model\Course;
use app\admin\model\Graduate;
use app\admin\model\Majorcourse;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
use think\Request;


class Graduates extends Base{

  public function index(){
    $user = $this->user_info();
    $arr = array(
      "setting" => $this->getAllSetting(),
      "course"=>Course::all()
    );
    return view("index",$arr);
  }

  public function toSearch(Request $request)
  {
    $user = $this->user_info();
    //接收搜索条件
    $data = $request->post();
    //组织条件
    $whereString = [];
    //学生姓名
    if (!empty($data["name"])) {
      $whereString['name'] = ['like', "%{$data['name']}%"];
    }
    //性别
    if ($data["sex"] != "") {
      $whereString['sex'] = $data["sex"];
    }
    //身份证
    if (!empty($data["idcards"])) {
      $whereString['idcard'] = ['like', "%{$data['idcard']}%"];
    }
    //民族
    if (!empty($data["nation"])) {
      $whereString['nation'] = ['like', "%{$data['nation']}%"];

    }
    //专业
    if (!empty($data["major"])) {
      $whereString['major'] = ['like', "%{$data['major']}%"];
    }
    //户籍所在地
    if (!empty($data["address"])) {
      $whereString['address'] = ['like', "%{$data['address']}%"];
    }
    //组织sql语句
    $list = collection(Graduate::where($whereString)->order("id", "asc")->select())->toArray();
    $str = "";
    if(!empty($list)) {
      foreach ($list as $v) {
        $print_time = empty($v['print_time'])? "未打印" : date('Y-m-d H:i:s',$v['print_time']);

        $str .= "
          <tr>
              <td>{$v['id']}</td>`
              <td>{$v['graduate_number']}</td>
              <td>{$v['name']}</td>
              <td>{$v['sex']}</td>
              <td>{$v['nation']}</td>
              <td>{$v['idcard']}</td>
              <td>{$v['address']}</td>
              <td>{$v['major']}</td>
              <td>{$print_time}</td>
              <td class=\"tools\">
                 <button class='print_first btn btn-xs btn-success' data-id='{$v['id']}'>第一页</button>
                 <button class='print_two btn btn-xs btn-info' data-id='{$v['id']}'>第二页</button>
                 <button class='print_three btn btn-xs  btn-danger' data-id='{$v['id']}' data-name='{$v['name']}'>第三页</button>
              </td>
          </tr>";
      }
    }else{
      $str .= "<tr><td colspan='10'>暂无数据</td></tr>";
    }
      $this->success($str);
  }

  //功能函数-----------------------------------------------
  //通过身份证获取年龄
  function getAgeByID($id){
    //过了这年的生日才算多了1周岁
    if(empty($id)) return '';
    $date=strtotime(substr($id,6,8));
    //获得出生年月日的时间戳
    $today=strtotime('today');
    //获得今日的时间戳 111cn.net
    $diff=floor(($today-$date)/86400/365);
    //得到两个日期相差的大体年数
    //strtotime加上这个年数后得到那日的时间戳后与今日的时间戳相比
    $age=strtotime(substr($id,6,8).' +'.$diff.'years')>$today?($diff+1):$diff;

    return $age;
  }
  //导入数据
  public function import()
  {
    //获取表格的大小，限制上传表格的大小5M
    if(isset($_FILES['inputExcelclass'])){
      $file_size = $_FILES['inputExcelclass']['size'];
      if ($file_size > 5 * 1024 * 1024) {
        $this->error('文件大小不能超过5M');
        exit();
      }
    }
    //限制上传表格类型
    if(isset($_FILES['inputExcelclass'])){
      $fileExtendName = substr(strrchr($_FILES['inputExcelclass']["name"], '.'), 1);
    }
    //application/vnd.ms-excel  为xls文件类型
    if ($fileExtendName != 'xls' && $fileExtendName != 'xlsx') {
      $this->error('必须为excel表格，且必须为xls格式！');
      exit();
    }
    // 有Xls和Xlsx格式两种
    if( $fileExtendName =='xlsx' )
    {
      $objReader = IOFactory::createReader('Xlsx');
    }else{
      $objReader = IOFactory::createReader('Xls');
    }
    $objReader->setReadDataOnly(TRUE);
    $filename = $_FILES['inputExcelclass']['tmp_name'];
    $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
    $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
    $highestRow = $sheet->getHighestRow();       // 取得总行数
    $highestColumn = $sheet->getHighestColumn();   // 取得总列数
    \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
    $lines = $highestRow -1;
    if($lines <= 0){
      $this->error('excel表格，没有数据！');
    }
    //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
    for ($j = 2; $j <= $highestRow; $j++) {
      $data[$j - 2] = [
        'graduate_number' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
        'name' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
        'sex' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
        'nation' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
        'idcard' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
        'address' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
        'major' => trim($objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue()),
      ];
    }
    //使用模型
    $Graduate = model('Graduate');
    $res = $Graduate->saveAll($data);
    if ($res) {
      $this->success('上传成功！');
    }
  }

  //打印毕业证-----------------------------------------------------------
  //毕业证第一页
  public function first_page(Request $request)
  {
    header('content-type:image/png');
    $user = $this->user_info();
    //查询用户信息和用户的报名信息
    $data = Graduate::get($request->get("id"));
    $width =1587;
    $height =2245;
    $img =imagecreatetruecolor($width,$height);
    $color = imagecolorallocate($img,255,255,255);
    $textcolor = imagecolorallocate($img,0,0,0);
    imagefill($img,0,0,$color);
    imagettftext($img, 40, 0, 1107, 880, $textcolor, ROOT_PATH."/public/static/admin/fonts/IRNazaninBold.otf", $data['graduate_number']);
    imagepng($img);
    imagedestroy($img);
    exit();
  }
  //毕业证第二页
  public function two_page(Request $request)
  {
    header('content-type:image/png');
    $user = $this->user_info();
    $data = Graduate::get($request->get("id"));
    $setting = $this->getAllSetting();
    $width =1587;
    $height =2245;
    $img =imagecreatetruecolor($width,$height);
    $color = imagecolorallocate($img,255,255,255);
    $textcolor = imagecolorallocate($img,0,0,0);
    imagefill($img,0,0,$color);
//    imagestring($img,57,434,1225,$apply_node,$textcolor);
    imagettftext($img, 28, 0, 1063, 177, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $data['name']);
    imagettftext($img, 28, 0, 1317, 177, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc",$data['sex']);
    imagettftext($img, 28, 0, 1509, 177, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $data['nation']);
    imagettftext($img, 28, 0, 997, 258, $textcolor, ROOT_PATH."/public/static/admin/fonts/IRNazaninBold.otf", $this->getAgeByID($data['idcard']));
    imagettftext($img, 28, 0, 1205, 258, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $data['address']);
    imagettftext($img, 28, 0, 974, 344, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['school_year']);
    imagettftext($img, 28, 0, 1238, 344, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['school_month']);
    imagettftext($img, 28, 0, 1410, 344, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['graduate_year']);
    imagettftext($img, 28, 0, 923, 424, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['graduate_month']);
    imagettftext($img, 28, 0, 1163, 424, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $data['major']);

    imagettftext($img, 28, 0, 1131, 1028, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['print_graduate_year']);
    imagettftext($img, 28, 0, 1353, 1028, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['print_graduate_month']);
    imagettftext($img, 28, 0, 1460, 1028, $textcolor, ROOT_PATH."/public/static/admin/fonts/simsun.ttc", $setting['print_graduate_day']);


    imagepng($img);
    imagedestroy($img);
    exit();
  }
  //毕业证第三页
  public function three_page($id)
  {
    header('content-type:image/jpeg');
    $user = $this->user_info();
    //查询用户信息和用户的报名信息
    $data = Graduate::get($id);
//    $data = Graduate::get(4);
    $major = Course::where("name",$data['major'])->field("id")->find();
    $majorCourse = Majorcourse::where("type",$major['id'])->select();
    $width =1587;
    $height =2245;
    $img =imagecreatetruecolor($width,$height);
    $color = imagecolorallocate($img,255,255,255);
    $textcolor = imagecolorallocate($img,0,0,0);
    imagefill($img,0,0,$color);

    for($i=0;$i<count($majorCourse);$i++) {
      $l = mb_strlen($majorCourse[$i]['name'],"utf-8");
      $x = 179+((216-($l*24))/2);
      $y = 290+$i*43.7-43.7;
//    imagestring($img,57,434,1225,$apply_node,$textcolor);
      imagettftext($img, 22, 0, 92, $y, $textcolor, ROOT_PATH . "/public/static/admin/fonts/IRNazaninBold.otf", $i+1);
      imagettftext($img, 22, 0, $x, $y, $textcolor, ROOT_PATH . "/public/static/admin/fonts/simsun.ttc", $majorCourse[$i]['name']);
      imagettftext($img, 22, 0, 491, $y, $textcolor, ROOT_PATH . "/public/static/admin/fonts/simsun.ttc", $majorCourse[$i]['course_time'] != 0 ?  $majorCourse[$i]['course_time'] : "");
      imagettftext($img, 22, 0, 639, $y, $textcolor, ROOT_PATH . "/public/static/admin/fonts/simsun.ttc", $majorCourse[$i]['name'] == "毕业设计" ? "优" : rand(75,95));
    }

    imagejpeg($img,null,100);
    imagedestroy($img);
    exit();
  }

//  function handleImg(){
//      header("content-type:image/jpeg"); //设置头部信息
//      $user = $this->user_info(); //判断是否登录
//      //查询用户信息和用户的报名信息
//      $data = Graduate::get(4);
//      $major = Course::where("name",$data['major'])->field("id")->find();
//      $majorCourse = Majorcourse::where("type",$major['id'])->select();
//     $color_transparent = new \ImagickPixel('#ffffff');
//     $width =1587;
//     $height =2245;
//     $image = new \Imagick(); //创建画布
//     $image->newImage($width,$height,$color_transparent,'jpg');
//     $style = ["font_size"=>150,"fill_color"=>"#000000","font"=>ROOT_PATH."/public/static/admin/fonts/IRNazaninBold.otf"];
//     $style2 = ["font_size"=>130,"fill_color"=>"#000000","font"=>ROOT_PATH."/public/static/admin/fonts/simsun.ttc"];
//     $image->resampleImage(300,300,\Imagick::RESOLUTION_PIXELSPERINCH,0);
//
//     for($i=0;$i<count($majorCourse);$i++) {
//          $str = $majorCourse[$i]['name'];
//          $l = mb_strlen($str, "utf-8");
//          $x = 800 + (1100-$l*130)/2;
//          $y = 1130 + $i * 192;
//          $this->add_text($image, $i+1, 410, $y, 1, $style); //序号
//          $this->add_text($image, $str, $x ,$y, 1, $style2); //课程名称 900
//          $this->add_text($image, $majorCourse[$i]['course_time'] != 0 ?  $majorCourse[$i]['course_time'] : "", 2200 ,$y, 1, $style); //课时
//          $this->add_text($image, $majorCourse[$i]['name'] == "毕业设计" ? "优" : rand(75,95), 2840 ,$y, 1, $style2); //成绩
//     }
//     echo $image;
//     $image->destroy();
//     exit;
//  }

  //设置文字
//  public function add_text(&$imagick, $text, $x = 0, $y = 0, $angle = 0, $style = array()) {
//    $draw = new \ImagickDraw ();
//    if (isset ( $style ['font'] ))
//      $draw->setFont ( $style ['font'] );
//    if (isset ( $style ['font_size'] ))
//      $draw->setFontSize ( $style ['font_size'] );
//    if (isset ( $style ['fill_color'] ))
//      $draw->setFillColor ( $style ['fill_color'] );
//    if (isset ( $style ['under_color'] ))
//      $draw->setTextUnderColor ( $style ['under_color'] );
//    if (isset ( $style ['font_family'] ))
//      $draw->setfontfamily( $style ['font_family'] );
//    if (isset ( $style ['font'] ))
//      $draw->setfont($style ['font'] );
//      $draw->settextencoding('UTF-8');
//    if (strtolower ($imagick->getImageFormat ()) == 'gif') {
//      foreach ( $imagick as $frame ) {
//        $frame->annotateImage ( $draw, $x, $y, $angle, $text );
//      }
//    } else {
//      $imagick->annotateImage ( $draw, $x, $y, $angle, $text );
//    }
//  }
  //保存打印时间
  public function print_time(Request $request){
    $res = Graduate::update($request->post());
    $this->success("打印完成");

  }
  //删除所有数据
  public function  delall(){
    $sql = "delete  from graduate";
    Db::execute($sql);
    Db::execute("alter table graduate  auto_increment = 0");
    $this->success("清空数据成功");

  }
  //待支付学生导出Excel表格
//  public function byzExcel()
//  {
//    $spreadsheet = new Spreadsheet();
//    $worksheet = $spreadsheet->getActiveSheet();
//    //设置工作表标题名称
//    $worksheet->setTitle('毕业证打印记录');
//
//    //表头
//    //设置单元格内容
//    $worksheet->setCellValueByColumnAndRow(1, 1, '毕业证打印记录');
//    $worksheet->setCellValueByColumnAndRow(1, 2, '毕业证编号');
//    $worksheet->setCellValueByColumnAndRow(2, 2, '姓名');
//    $worksheet->setCellValueByColumnAndRow(3, 2, '性别');
//    $worksheet->setCellValueByColumnAndRow(4, 2, '民族');
//    $worksheet->setCellValueByColumnAndRow(5, 2, '身份证号');
//    $worksheet->setCellValueByColumnAndRow(6, 2, '家庭住址');
//    $worksheet->setCellValueByColumnAndRow(7, 2, '专业');
//    $worksheet->setCellValueByColumnAndRow(8, 2, '打印时间');
//
//
//    //合并单元格
//    $worksheet->mergeCells('A1:H1');
//
//    $styleArray = [
//      'font' => [
//        'bold' => true
//      ],
//      'alignment' => [
//        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
//      ],
//    ];
//    //设置单元格样式
//    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(20);
//
//    $worksheet->getStyle('A2:H2')->applyFromArray($styleArray)->getFont()->setSize(14);
//    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
//    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
//    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
//    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
//    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
//    $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(30);
//    $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(27);
//    $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(27);
//    $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(30);
//
//
//
//    //查询所有录取信息
//    $newslist = collection(Graduate::order("print_time asc")->select())->toArray();
//    $len = count($newslist);
//    $j = 0;
//    for ($i = 0; $i < $len; $i++) {
//      $print_time = empty($print_time) ? "未打印" : date('Y-m-d H:i:s',$newslist[$i]['print_time']);
//      $j = $i + 3; //从表格第3行开始
//      $worksheet->setCellValueByColumnAndRow(1, $j, $newslist[$i]['graduate_number']."\t");
//      $worksheet->setCellValueByColumnAndRow(2, $j, $newslist[$i]['name']);
//      $worksheet->setCellValueByColumnAndRow(3, $j, $newslist[$i]['sex']);
//      $worksheet->setCellValueByColumnAndRow(4, $j, $newslist[$i]['nation']);
//      $worksheet->setCellValueByColumnAndRow(5, $j, $newslist[$i]['idcard'] . "\t");
//      $worksheet->setCellValueByColumnAndRow(6, $j, $newslist[$i]['address']);
//      $worksheet->setCellValueByColumnAndRow(7, $j, $newslist[$i]['major']);
//      $worksheet->setCellValueByColumnAndRow(8, $j, $print_time);
//    }
//    $styleArrayBody = [
//      'borders' => [
//        'allBorders' => [
//          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
//          'color' => ['argb' => '666666'],
//        ],
//      ],
//      'alignment' => [
//        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
//      ],
//    ];
//
//    $total_jzInfo = $len + 2;
//    //添加所有边框/居中
//    $worksheet->getStyle('A1:H' . $total_jzInfo)->applyFromArray($styleArrayBody);
//
//
//    $filename = '毕业证打印记录' . date("Y-m-d(His)") . '.xlsx';
//    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//    header('Content-Disposition: attachment;filename="' . $filename . '"');
//    header('Cache-Control: max-age=0');
//
//    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
//    $writer->save('php://output');
//  }

  //导出数据
  public function byzExcel()
  {
    set_time_limit(0);
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    //设置工作表标题名称
    $worksheet->setTitle('历年各专业最低分');

    //表头
    //设置单元格内容
    $worksheet->setCellValueByColumnAndRow(1, 1, '历年各专业最低分');
    $worksheet->setCellValueByColumnAndRow(1, 2, '学校名称');
    $worksheet->setCellValueByColumnAndRow(2, 2, '专业名称');
    $worksheet->setCellValueByColumnAndRow(3, 2, '科类');
    $worksheet->setCellValueByColumnAndRow(4, 2, '2019年');
    $worksheet->setCellValueByColumnAndRow(5, 2, '2020年');
    $worksheet->setCellValueByColumnAndRow(6, 2, '2021年');



    //合并单元格
    $worksheet->mergeCells('A1:E1');

    $styleArray = [
      'font' => [
        'bold' => true
      ],
      'alignment' => [
        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];
    //设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(20);

    $worksheet->getStyle('A2:H2')->applyFromArray($styleArray)->getFont()->setSize(14);
    $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
    $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);




    //查询所有录取信息
    $sql = 'select t1.name,t1.major,t1.type,t1.score as \'2021\',t2.score as \'2020\',t3.score as \'2019\' from fsx as t1 left join fsx as t2 on t1.years = 2021 and t2.years=2020 and  t1.name = t2.name  and (t2.major like concat("%",t1.major,"%") or t1.major like concat("%",t2.major,"%"))  left join fsx as t3 on t3.years = 2019  and t2.name = t3.name and  (t2.major like concat("%",t3.major,"%") or t3.major like concat("%",t2.major,"%"))';
//    $sql  = "select *,score as '2019' from fsx";
    $newslist = Db::query($sql);
//    $newslist = collection(Graduate::order("print_time asc")->select())->toArray();
    $len = count($newslist);
    $j = 0;
    for ($i = 0; $i < $len; $i++) {
//      $print_time = empty($print_time) ? "未打印" : date('Y-m-d H:i:s',$newslist[$i]['print_time']);
      $j = $i + 3; //从表格第3行开始s
      $worksheet->setCellValueByColumnAndRow(1, $j, $newslist[$i]['name']."\t");
      $worksheet->setCellValueByColumnAndRow(2, $j, $newslist[$i]['major']);
      $worksheet->setCellValueByColumnAndRow(3, $j, $newslist[$i]['type']);
      $worksheet->setCellValueByColumnAndRow(4, $j, $newslist[$i]['2019']);
//      $worksheet->setCellValueByColumnAndRow(5, $j, $newslist[$i]['2020']);
//      $worksheet->setCellValueByColumnAndRow(6, $j, $newslist[$i]['2021']);
    }
    $styleArrayBody = [
      'borders' => [
        'allBorders' => [
          'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
          'color' => ['argb' => '666666'],
        ],
      ],
      'alignment' => [
//        'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
      ],
    ];

    $total_jzInfo = $len + 2;
    //添加所有边框/居中
    $worksheet->getStyle('A1:F' . $total_jzInfo)->applyFromArray($styleArrayBody);


    $filename = '历年各专业最低分' . date("Y-m-d(His)") . '.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
  }

}
